package myta.bean;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.faces.component.html.HtmlInputText;
import javax.faces.component.html.HtmlSelectOneMenu;
import javax.faces.model.SelectItem;

import myta.model.DataRow;
import myta.model.DataTable;
import myta.service.Command;
import myta.service.SQLexec;
import myta.service.Utility;

public class LaporanAbsensi {
	private HtmlInputText txtthnajaran;
	private HtmlInputText txtstartdate;
	private HtmlInputText txtenddate;
	private HtmlSelectOneMenu txtidkelas;
	private HtmlSelectOneMenu txtnis;
	
	private DataTable datatable;
	
	private List<SelectItem> listKelas;
	private List<SelectItem> listNis;
	
	public LaporanAbsensi() {
		try {
			txtthnajaran = new HtmlInputText();
			txtidkelas = new HtmlSelectOneMenu();
			txtnis = new HtmlSelectOneMenu();
			txtstartdate = new HtmlInputText();
			txtenddate = new HtmlInputText();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			txtthnajaran.setValue("1995");
			
			sdf = new SimpleDateFormat("yyyy-MM-dd");
			txtstartdate.setValue(sdf.format(Calendar.getInstance().getTime()));
			txtenddate.setValue(sdf.format(Calendar.getInstance().getTime()));
			
			loadListDefault();
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	private void loadListDefault() throws Exception{
		listNis = new ArrayList<SelectItem>();
		listKelas = new ArrayList<SelectItem>();
		
		
		SelectItem s = new SelectItem();
		s.setValue("-");
		s.setLabel("All");
		
		listKelas.add(s);
		listNis.add(s);
		
		String sql = "select nis,nama from siswa";
		Command command = SQLexec.makeCommand(sql);
		DataTable dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("nis"));
			s.setLabel(dr.getString("nama"));
			
			listNis.add(s);
		}
		
		sql = "select idkelas,namakelas from kelas";
		command = SQLexec.makeCommand(sql);
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("idkelas"));
			s.setLabel(dr.getString("namakelas"));
			
			listKelas.add(s);
		}
	}
	
	private void loadList(String thnajaran, String idkelas) throws Exception{
		listNis = new ArrayList<SelectItem>();
		listKelas = new ArrayList<SelectItem>();
		
		
		SelectItem s = new SelectItem();
		s.setValue("-");
		s.setLabel("All");
		
		listKelas.add(s);
		
		if(idkelas.equalsIgnoreCase("")){
			listNis.add(s);
		}
		
		String sql = "select distinct sw.nis,sw.nama from jadwal j inner join mapel m on m.idmapel = j.idmapel inner join kelassiswa ks on j.idkelassiswa = ks.idkelassiswa inner join kelas k on ks.idkelas=k.idkelas inner join semester s on k.idsemester=s.idsemester inner join kelassiswa_detail ksd on ks.idkelassiswa = ksd.idkelassiswa inner join siswa sw on sw.nis = ksd.nis where s.thnajaran = ? and k.idkelas like ?";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, Integer.valueOf(thnajaran));
		command.setObject(2, "%"+idkelas+"%");
		DataTable dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("nis"));
			s.setLabel(dr.getString("nama"));
			
			listNis.add(s);
		}
		
		sql = "select distinct k.idkelas,k.namakelas from jadwal j inner join mapel m on m.idmapel = j.idmapel inner join kelassiswa ks on j.idkelassiswa = ks.idkelassiswa inner join kelas k on ks.idkelas=k.idkelas inner join semester s on k.idsemester=s.idsemester where s.thnajaran = ? and k.idkelas like ?";
		command = SQLexec.makeCommand(sql);
		command.setObject(1, Integer.valueOf(thnajaran));
		command.setObject(2, "%"+idkelas+"%");
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("idkelas"));
			s.setLabel(dr.getString("namakelas"));
			
			listKelas.add(s);
		}
	}
	
	private void loadDatatable(String thnajaran, String nis, String idkelas, String startdate, String enddate) throws Exception{
		datatable = new DataTable();
		
		String sql = "select distinct sm.thnajaran, s.nis, s.nama, a.tanggal, m.namamapel, ";
		sql += "case when ad.absensi = 0 then 'Tidak Masuk' else 'Masuk' end as absensi from absen a ";
		sql += "inner join absen_detail ad on a.idabsen = ad.idabsen ";
		sql += "inner join siswa s on ad.nis = s.nis ";
		sql += "inner join mapel m on a.idmapel = m.idmapel ";
		sql += "inner join kelassiswa ks on a.idkelassiswa = ks.idkelassiswa ";
		sql += "inner join kelas k on ks.idkelas = ks.idkelas ";
		sql += "inner join semester sm on k.idsemester = sm.idsemester ";
		sql += "where sm.thnajaran = ? and s.nis like ? and k.idkelas like ? and (a.tanggal >= ? and a.tanggal <= ?) ";
		sql += "order by sm.thnajaran, s.nis, s.nama, a.tanggal";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, Integer.valueOf(thnajaran));
		command.setObject(2, nis.equalsIgnoreCase("-")?"%%":"%"+nis+"%");
		command.setObject(3, idkelas.equalsIgnoreCase("-")?"%%":"%"+idkelas+"%");
		command.setObject(4, startdate);
		command.setObject(5, enddate);
		
		datatable = command.executeQuery();
	}
	
	public void thnajaranonchange(){
		try {
			loadList(txtthnajaran.getValue().toString(), "");
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	public void idkelasonchange(){
		try {
			loadList(txtthnajaran.getValue().toString(), txtidkelas.getValue().toString());
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	public void proses(){
		try {
			loadDatatable(txtthnajaran.getValue().toString(), txtnis.getValue().toString(), txtidkelas.getValue().toString(), txtstartdate.getValue().toString(), txtenddate.getValue().toString());
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}

	public HtmlInputText getTxtthnajaran() {
		return txtthnajaran;
	}

	public void setTxtthnajaran(HtmlInputText txtthnajaran) {
		this.txtthnajaran = txtthnajaran;
	}

	public HtmlSelectOneMenu getTxtidkelas() {
		return txtidkelas;
	}

	public void setTxtidkelas(HtmlSelectOneMenu txtidkelas) {
		this.txtidkelas = txtidkelas;
	}
	
	public HtmlSelectOneMenu getTxtnis() {
		return txtnis;
	}

	public void setTxtnis(HtmlSelectOneMenu txtnis) {
		this.txtnis = txtnis;
	}

	public DataTable getDatatable() {
		return datatable;
	}

	public void setDatatable(DataTable datatable) {
		this.datatable = datatable;
	}



	public List<SelectItem> getListKelas() {
		return listKelas;
	}



	public void setListKelas(List<SelectItem> listKelas) {
		this.listKelas = listKelas;
	}

	public List<SelectItem> getListNis() {
		return listNis;
	}



	public void setListNis(List<SelectItem> listNis) {
		this.listNis = listNis;
	}

	public HtmlInputText getTxtstartdate() {
		return txtstartdate;
	}

	public void setTxtstartdate(HtmlInputText txtstartdate) {
		this.txtstartdate = txtstartdate;
	}

	public HtmlInputText getTxtenddate() {
		return txtenddate;
	}

	public void setTxtenddate(HtmlInputText txtenddate) {
		this.txtenddate = txtenddate;
	}
}
